Release 10.1A: OpenEdge Data Management:
SQL Reference

Character data types

Character data strings consist of a sequence of character from a defined character set, such as ASCII. A character string may have a fixed or varying length.

This is the syntax for a Character data types:

Syntax

{ CHARACTER | CHAR } [ ( length ) ] | { CHARACTER VARYING | CHAR VARYING | VARCHAR } [ ( length ) ]

CHARACTER [ ( length ) ]

CHARACTER (alias CHAR) corresponds to a null-terminated character string with the length specified. Values are padded with blanks to the specified length. The default length is 1. The maximum length is 2,000 characters.

The OpenEdge SQL representation is a variable-length string. The host language representation is equivalent to a C language character string.

{ CHARACTER VARYING | CHAR VARYING | VARCHAR } [ ( length ) ]

CHARACTER VARYING, CHAR VARYING, and VARCHAR corresponds to a variable-length character string with the maximum length specified. The default length is 1 character. The maximum length is 31,995 characters.

Notes

For data types CHARACTER( length ) and VARCHAR( length ) the value of length specifies the number of characters.
The maximum length can be as large as 31,995. The sum of all the column lengths of a table row must not exceed 31,960.
Due to index size limitations, only the narrower VARCHAR columns can be indexed.

Maximum length for VARCHAR

The maximum length of the VARCHAR data type depends on:

The number of columns in a table — More columns in a table further limits the length of VARCHAR data.
When a table was created — Tables created earlier can support longer VARCHAR data than tables created later.

National Language Support (NLS)

The VARCHAR data type has NLS. The choice of character set affects the available character count or maximum length of the data column. The limits established above assume a single–byte character set. Using a multiple–byte character set lowers the maximum character count proportionally. For example, if all the characters in a character set take 3 bytes per character, the practical maximum is 10,660 (31,982 divided by 3). If, however, you are using a variable–width character set, you will be able to hold between 10,660 and 31,982 characters, depending on the actual mix of characters you use.

Concatenation operator

Use the concatenation operator (||) to join two text strings together.

Example 2 provides an example of a concatenation operator used in a query:

Example 2: Concatenation operator

SELECT firstname || ' ' || lastname from Employee;